#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Software : PyCharm
# @Time    : 2024-12-18 11:08
# @Author  : gzh
# @File : ExcelCreate.py

"""
对openpyxl的部分功能进行了一下简单的封装
"""
from openpyxl import Workbook, load_workbook  # noqa
from openpyxl.utils import get_column_letter  # noqa
from openpyxl.styles import Font, Border, Side, Alignment, PatternFill


class ExcelCreate(object):
    __instance = None

    def __new__(cls, *args, **kwargs):
        if cls.__instance is None:
            cls.__instance = object.__new__(cls)
        return cls.__instance

    excel_page_height = 690
    excel_page_width = 86
    excel_row_height = 20
    excel_top_height = 40
    excel_title_height = 25

    wb = None
    font26 = Font(name='黑体', size=26, bold=True, italic=False, vertAlign=None,
                  underline='none', strike=False, color="000000")
    font20 = Font(name='黑体', size=20, bold=True, italic=False, vertAlign=None,
                  underline='none', strike=False, color="000000")
    font16 = Font(name='黑体', size=16, bold=False, italic=False, vertAlign=None,
                  underline='none', strike=False, color="000000")
    font16_ = Font(name='黑体', size=16, bold=True, italic=False, vertAlign=None,
                   underline='none', strike=False, color="000000")
    font12 = Font(name='黑体', size=12, bold=False, italic=False, vertAlign=None,
                  underline='none', strike=False, color="000000")
    font12_ = Font(name='黑体', size=12, bold=True, italic=False, vertAlign=None,
                   underline='none', strike=False, color="000000")
    font10 = Font(name='黑体', size=10, bold=False, italic=False, vertAlign=None,
                  underline='none', strike=False, color="000000")
    font10_ = Font(name='黑体', size=10, bold=True, italic=False, vertAlign=None,
                   underline='none', strike=False, color="000000")
    font18k_ = Font(name='黑体', size=18, bold=True, italic=False, vertAlign=None,
                    underline='none', strike=False, color="000000")
    font12k = Font(name='黑体', size=12, bold=False, italic=False, vertAlign=None,
                   underline='none', strike=False, color="000000")
    font12k_ = Font(name='黑体', size=12, bold=True, italic=False, vertAlign=None,
                    underline='none', strike=False, color="000000")
    alignment = Alignment(horizontal='center', vertical='center', wrapText=True)  # 左右居中 上下居中
    alignment1 = Alignment(horizontal='right', vertical='center', wrapText=True)  # 左右居右 上下居中
    alignment2 = Alignment(horizontal='left', vertical='center', wrapText=True)  # 左右居左 上下居中
    alignment3 = Alignment(horizontal='distributed', vertical='center', wrapText=True)  # 左右分散对齐 上下居中
    # {'centerContinuous', 'right', 'distributed', 'left', 'center', 'justify', 'fill', 'general'}
    thin = Side(border_style="thin", color="000000")
    bold = Side(border_style="thick", color="000000")
    none = Side(border_style="none")
    '''
    thick：粗线。这是相对较粗的边框线型，用于需要强调的边框。
    medium：中等粗细的线。介于粗线和细线之间。
    hair：极细的线，几乎像头发丝一样细。这种线型在视觉上可能不是很明显。
    mediumDashDot：中等粗细的虚线和点交替的线型。这种线型在视觉上提供了更多的变化。
    dashed：虚线。由一系列短线段组成，中间有空隙。
    double：双线。由两条平行的线组成，通常用于表示更强的边框。
    mediumDashDotDot：中等粗细的虚线、点、再虚线交替的线型。比 mediumDashDot 多了一个点。
    dashDot：虚线和点交替的线型，但具体粗细和样式可能因实现而异。
    mediumDashed：中等粗细的虚线。与 dashed 类似，但强调其粗细为中等。
    dotted：点线。由一系列点组成，没有连续的线段。
    thin：细线。这是默认的或最细的边框线型。
    slantDashDot：斜向的虚线和点交替的线型。这种线型可能具有斜向的视觉效果。
    dashDotDot：虚线、点、再点的交替线型。与 dashDot 相比，多了一个点。
    '''
    border_c = Border(top=thin, left=thin, right=thin, bottom=thin)
    border_n = Border(top=bold, left=thin, right=thin, bottom=thin)
    border_s = Border(top=thin, left=thin, right=thin, bottom=bold)
    border_e = Border(top=thin, left=thin, right=bold, bottom=thin)
    border_w = Border(top=thin, left=bold, right=thin, bottom=thin)
    border_ne = Border(top=bold, left=thin, right=bold, bottom=thin)
    border_nes = Border(top=bold, left=thin, right=bold, bottom=bold)
    border_nw = Border(top=bold, left=bold, right=thin, bottom=thin)
    border_ns = Border(top=bold, left=thin, right=thin, bottom=bold)
    border_nws = Border(top=bold, left=bold, right=thin, bottom=bold)
    border_se = Border(top=thin, left=thin, right=bold, bottom=bold)
    border_sw = Border(top=thin, left=bold, right=thin, bottom=bold)
    border_ww = Border(top=bold, left=bold, right=bold, bottom=bold)
    border_nwe = Border(top=bold, left=bold, right=bold, bottom=thin)

    border_c_none = Border(top=none, left=none, right=none, bottom=none)
    border_n_none = Border(top=bold, left=none, right=none, bottom=none)
    border_nw_none = Border(top=bold, left=bold, right=none, bottom=none)
    border_ne_none = Border(top=bold, left=none, right=bold, bottom=none)
    border_s_none = Border(top=none, left=none, right=none, bottom=bold)
    border_sw_none = Border(top=none, left=bold, right=none, bottom=bold)
    border_se_none = Border(top=none, left=none, right=bold, bottom=bold)
    border_w_none = Border(top=none, left=bold, right=none, bottom=none)
    border_e_none = Border(top=none, left=none, right=bold, bottom=none)

    fill1 = PatternFill(start_color="C8C8C8", end_color="C8C8C8", fill_type="solid")  # 灰色
    fill2 = PatternFill(start_color="FFFFFF", end_color="FFFFFF", fill_type="solid")  # 白色
    fill3 = PatternFill(start_color="DB903F", end_color="DB903F", fill_type="solid")  # 黄色
    fill4 = PatternFill(start_color="FF1B1B", end_color="FF1B1B", fill_type="solid")  # 红色
    fill5 = PatternFill(start_color="1CB30D", end_color="1CB30D", fill_type="solid")  # 绿色

    def __init__(self, src_xlsx='', name='', data_only=True):
        try:
            ExcelCreate.wb, self.sheet_names = ExcelCreate.copy_sheet(src_xlsx, name, data_only=data_only)
        except Exception as e:
            print(e)
            ExcelCreate.wb = Workbook()
            self.sheet_names = []

    @classmethod
    def excel_set_2(cls, ws, row, column):  # 设置全部字体线条
        for i in range(1, row):
            if i > 2:
                ws.row_dimensions[i].height = 18
                for j in range(1, cls.column_letter_to_number(column) + 1):
                    ws[f'{get_column_letter(j)}{i}'].border = cls.border_c
                    ws[f'{get_column_letter(j)}{i}'].alignment = cls.alignment
                    ws[f'{get_column_letter(j)}{i}'].font = cls.font12

    @classmethod
    def all_write_data_save(cls, path):  # 保存表格
        cls.wb.save(path)

    @classmethod
    def excel_set_border(cls, ws, row, column):
        for i in range(1, cls.column_letter_to_number(column) + 1):
            for j in range(1, row):
                ws.column_dimensions[f'{get_column_letter(i)}{j}'].border = cls.border_c

    @staticmethod
    def column_letter_to_number(column_letter):
        """
        将Excel列字母转换为数字。
        参数: column_letter: 字符串，表示Excel列（例如'A', 'B', 'Z', 'AA', 'AB', ...）
        返回: int: 对应的列数字
        """
        number = 0
        for char in column_letter:
            number = number * 26 + (ord(char) - ord('A') + 1)
        return number

    @staticmethod
    def xian_shi_wei(a, a1=100, a2=20, a3=1):
        a = float(a)
        if a >= float(a1):
            last_a = '%.1f' % a
        elif a >= float(a2):
            last_a = '%.2f' % a
        elif a >= float(a3):
            last_a = '%.3f' % a
        else:
            last_a = '%.4f' % a
        return last_a

    @staticmethod
    def copy_sheet(src_xlsx, name, data_only=True):
        """复制sheet工作表到另一个工作簿
        :param src_xlsx:源xlsx工作簿文件(相对/绝对目录及文件名)包含xlsx后缀 不存在则报错 字符串类型
        :param name:源工作表名 不存在则报错 字符串类型
        :param data_only:如果为True,则数据保留为数值，否则False则保存为公式．
        :return:
        """
        try:
            wb = load_workbook(f'{src_xlsx}', data_only=data_only)
            sheets = wb.sheetnames
        except Exception as e:
            raise Exception(e)

        for sh in sheets:
            if sh != name:
                del wb[sh]
        return wb, sheets

    @staticmethod
    def get_value_from_excel_sheet_no(file_path, r_start=0, column_name='A', sheet_no=0):
        """
        :param file_path: excel文件路径  后缀为.xlsx  如果是xls会报错可通过wps软件将xls文件转为xlsx文件
        :param r_start: 读取开始行
        :param column_name: 读取那一列数据
        :param sheet_no: Excel下的第几个sheet
        :return: 读取列数据
        """
        workbook = load_workbook(filename=file_path, data_only=True)
        sheet = workbook.worksheets[sheet_no]
        return [cell.value for cell in sheet[column_name]][r_start:]


if __name__ == '__main__':
    app = ExcelCreate()
    app.column_letter_to_number('AA')
